﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.OracleClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace YouDaoNotesCodes
{
    class ConnectOracle
    {
        /*问题和解决方式：
         * 1、System.Data.OracleClient.OracleConnection”已过时；添加Oracle.DataAccess.dll引用；
         * 2、system.data.oracleclient 需要 oracle 客户端软件 8.1.7 或更高版本；复制oci.dll、oraociei11.dll、Oracle.DataAccess.dll到bin的Debug下
         * 3、尝试加载 Oracle 客户端库时引发 BadImageFormatException。如果在安装 32 位 Oracle 客户端组件的情况下以 64 位模式运行，将出现此问题；未解决！！！
         * https://www.cnblogs.com/joey0210/archive/2012/08/15/2640248.html
         */
        public void ReadFromOracleTest()
        {
            string conn = string.Concat(
                @"Data Source=",
                @"    (DESCRIPTION=",
                @"        (ADDRESS_LIST=",
                @"            (ADDRESS=",
                @"                (PROTOCOL=TCP)",
                @"                (HOST=10.24.18.12)",
                @"                (PORT=1521)",
                @"            )",
                @"        )",
                @"        (CONNECT_DATA=",
                @"            (SERVICE_NAME=orcl)",
                @"        )",
                @"    );",
                @"Persist Security Info=True;",
                @"User Id=lc0019999;",
                @"Password=aaaaaa"
                );
            OracleConnection oc = new OracleConnection(conn);
            try
            {
                oc.Open();
                string sql = "select sysdate from dual";
                OracleCommand cm = new OracleCommand(sql, oc);
                //OracleCommand cmd = oc.CreateCommand();
                //cmd.CommandText = "select sysdate from dual";
                OracleDataReader odr = cm.ExecuteReader();
                while (odr.Read())
                {
                    Console.WriteLine(odr.GetString(0));//odr.GetOracleDateTime(0).ToString()                
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                oc.Close();
            }
        }

        //此方法可以使用，亲测无误
        #region 连接数据库:服务器下生效
        public int ConnectOra(string usercode)
        {
            int num = 0;
            string connectora = "Provider=OraOLEDB.Oracle;Password=aaaaaa;User ID=lc0019999;Data Source=ORCL;Persist Security Info=True";//此处需要数据库用户名，密码，服务名
            OleDbConnection conn = new OleDbConnection();
            conn.ConnectionString = connectora;

            string sql = string.Format(@"SELECT COUNT(CODE)NUM FROM GSPUSER WHERE CODE = '{0}' ", usercode);
            OleDbCommand cmdOra = new OleDbCommand(sql, conn);
            PubCommon.WriteLog(sql, System.Reflection.MethodBase.GetCurrentMethod().Name);
            try
            {
                conn.Open();
                PubCommon.WriteLog(cmdOra.ExecuteScalar().ToString(), System.Reflection.MethodBase.GetCurrentMethod().Name);
                num = int.Parse(cmdOra.ExecuteScalar().ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cmdOra.Connection.State == ConnectionState.Open)
                {
                    cmdOra.Connection.Close();
                    cmdOra.Connection.Dispose();
                    cmdOra.Parameters.Clear();
                    cmdOra.Dispose();
                }

            }
            return num;
        }
        #endregion

        #region  配置文件写明连接属性和方法1一样的
        public void OraConnect()
        {
            string connstr = ConfigurationManager.ConnectionStrings["ORACLEConnectionString"].ConnectionString;
            //todo:SERVER写成SERVER_NAME会报出 ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务
            //password错误会提示：ORA-01017: 用户名/口令无效; 登录被拒绝 
            //没有写(SID = DB11G)会提示：ORA-12504: TNS: 监听程序在 CONNECT_DATA 中未获得 SERVICE_NAME
            //没写(SERVER=DEDICATED)会提示：妈的！可以成功返回
            //没写DESCRIPTION =(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.51.108)(PORT = 1521)))  ORA-12533: TNS: 非法的 ADDRESS 参数
            //HOST地址配错一般会提示连接超时
            //从网上复制的这种 string sqlcon = @"Data Source=TestDB;Persist Security Info=True;User ID=tanyixiu;Password=pwd;Unicode=True";，他妈的绝对是不行的！
            //只要提示是DRA开头，说明你已经和oracle发生“握手”了，如果出现System.Data.OracleClient 需要 Oracle 客户端软件 version 8.1.7 或更高版本说明没有完成Instant Client的安装或环境变量的配置
            //从昨天下午开始搞这个，到16：00才连上oracle，我觉得是上帝玩够了，不想和你开玩笑了，所以你就成功了。
            //var connectionString = @"Data Source=(DESCRIPTION =(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.51.108)(PORT = 1521)))(CONNECT_DATA =(SERVER = DEDICATED)(SID = DB11G)));User Id=123;Password=123;Persist Security Info=True;";//

            OracleConnection oc = new OracleConnection(connstr);
            oc.Open();
            OracleDataAdapter od = new OracleDataAdapter("select * from tb_zwq", connstr);
            DataSet ds = new DataSet();
            od.Fill(ds);
            Console.WriteLine("数据库连接成功");
        }
        #endregion

    }
}
